package com.mcay.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.mcay.easyexcel.entity.Student;
import com.mcay.easyexcel.service.StudentService;
import com.mcay.easyexcel.utils.EasyexcelUtil;
import com.mcay.easyexcel.utils.ResultVOUtil;
import com.mcay.easyexcel.vo.ResultVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 *
 * Created by  longHe
 * 2021/8/20 18:01
 */
@Slf4j
@Controller
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @RequestMapping(value = "/test", method = RequestMethod.GET)
    @ResponseBody
    public void test() {
        System.out.println("sss");
        System.out.println("jjj");
    }


    /**
     * 分页获取学生信息
     * @param object {pageNum,pageSize,查询条件}
     * @return ResultVO
     */
    @RequestMapping(value = "/getStudentInfoPage", method = RequestMethod.POST)
    @ResponseBody
    public ResultVO<Object> getStudentInfoPage(@RequestBody JSONObject object) {
        List<?> list = new ArrayList<>();
        try {
            //获取页码，默认第一页
            int pageNum = object.get("pageNum") != null ? Integer.parseInt(object.getString("pageNum")) : 1;
            //获取每页显示条数，默认20
            int pageSize = object.get("pageSize") != null ? Integer.parseInt(object.getString("pageSize")) : 20;
            //获取排序方式
            String orderBy = object.get("orderBy") != null ? object.getString("orderBy") : null;
            //获取查询条件
            String school = object.get("school") != null ? object.get("school").toString() : "麓谷外星人小学";
            Map<String, String> map = new HashMap<>();
            map.put("school", school);
            //count：查询总数、isReasonable分页合理化、isPageSizeZero是否支持PageSize为0，true且pageSize=0时返回全部结果
            Page<?> page = PageHelper.startPage(pageNum, pageSize, true, true, true);
            //Page<?> page = PageHelper.startPage(pageNum, pageSize, orderBy); //orderBy:排序方式   慎用
            list =  studentService.findeStudentPage(map);
            System.out.println("总记录数:" + page.getTotal() + ",总页数:" + page.getPages() + ",每页条数:" + page.getPageSize()+ ",当前页码:" + page.getPageNum());

            return ResultVOUtil.success(list);
        } catch (Exception e) {
            log.error("查询数据失败,function=getStudentInfo,失败原因：{}", e.toString());
            e.printStackTrace();
            return ResultVOUtil.error();
        }

    }



    /**
     * 插入测试数据 学生信息
     * @return ResultVO
     */
    @RequestMapping("/insertStudentTest")
    @ResponseBody
    public ResultVO<Object> insertStudentTest() throws ParseException {
        List<Student> studentList = new ArrayList<>();
        try {
            for (int i = 1 ; i <= 143; i ++) {
                Student student = new Student();
                student.setId(UUID.randomUUID().toString().replaceAll("-", ""));
                student.setName(String.valueOf(i));
                student.setAge(i);
                student.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("2000-01-01"));
                student.setSex("男");
                student.setSchool("麓谷外星人小学");
                student.setClazz("外星人" + i + "班");
                student.setSl(0.67);

                //其他信息（只是为了凑excel列数）
                student.setX1("其他信息" + i);
                student.setX2("其他信息" + i);
                student.setX3("其他信息" + i);
                student.setX4("其他信息" + i);
                student.setX5("其他信息" + i);
                student.setX6("其他信息" + i);
                student.setX7("其他信息" + i);
                student.setX8("其他信息" + i);
                student.setX9("其他信息" + i);
                student.setX10("其他信息" + i);
                student.setX11("其他信息" + i);
                student.setX12("其他信息" + i);
                student.setX13("其他信息" + i);
                student.setX14("其他信息" + i);
                student.setX15("其他信息" + i);
                studentList.add(student);
            }
            //studentService.batchInsertStudent(studentList);
            return ResultVOUtil.success();
        } catch (Exception e) {
            log.error("批量插入失败！失败原因：{}", e.toString());
            e.printStackTrace();
            return ResultVOUtil.error("操作失败，function:insertStudentTest，失败原因：" + e.toString());
        }
    }


    /**
     * 使用easyexcel生成excel
     * 15级别数据性能测试
     * @return resultvo
     */
    @RequestMapping("/simpleWrite15w")
    @ResponseBody
    public ResultVO<Object> simpleWrite15w() {
        try {
            //data:插入excel的数据
            long start = System.nanoTime();
            List<?> studentList = studentService.findeStudentPage(null);
            String fileName = "C:\\Users\\花花很Nice\\Desktop\\easyexcel\\student.xlsx";
            String sheetName = "信息汇总";
            //开始生成excel
            EasyexcelUtil.simpleWrite(studentList, Student.class, fileName, sheetName);
            long end = System.nanoTime();
            long s = (end - start)/1000/1000/1000;
            long m= (end - start)/1000/1000/1000/60;
            System.out.println("生成excel条数：" + studentList.size() + "，生成excel共耗时：" + s + "秒，约：" + m + "分钟");

            return ResultVOUtil.success();
        } catch (Exception e) {
            log.error("生成excel！失败原因：{}", e.toString());
            e.printStackTrace();
            return ResultVOUtil.error();
        }
    }


    /**
     * 实现分批次写入
     * @param object {pageNum:页码,pageSize:分批条数,fileName:文件名称,sheetName:页签名称}
     */
    @RequestMapping(value = "/repeatedWrite15w", method = RequestMethod.POST)
    @ResponseBody
    public ResultVO<Object> repeatedWrite15w(@RequestBody JSONObject object) {
        try {
            //获取页码，默认第一页
            int pageNum = object.get("pageNum") != null ? Integer.parseInt(object.getString("pageNum")) : 1;
            //获取分批条数，默认2000
            int pageSize = object.get("pageSize") != null ? Integer.parseInt(object.getString("pageSize")) : 20;
            //获取查询条件
            String school = object.get("school") != null ? object.get("school").toString() : "麓谷外星人小学";
            //获取excel相关条件
            String fileName = object.get("fileName") != null ? object.get("fileName").toString() : "E:\\data.xlsx";
            String sheetName = object.get("sheetName") != null ? object.get("sheetName").toString() : "sheet1";

            long start = System.nanoTime();

            Map<String, String> paramMap = new HashMap<>();
            paramMap.put("school", school);
            //第一次查询作用：获取pages
            Page<?> page = PageHelper.startPage(pageNum, pageSize, true);
            studentService.findeStudentPage(paramMap);
            //获取总页数，用于分批查询和分批生成excel
            long pages = page.getPages();

            List<List<String>> headList = null;

            //easyexcel 实现分批次写入 ---> 当查询第一页之后写入第一页，当查询完第二页之后写入第二页 .....
            ExcelWriter excelWriter = EasyExcel.write(fileName).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(headList).build();
            for(int i = 0; i < pages; i ++) {
                PageHelper.startPage(i + 1, pageSize, true);
                List<Map<String, Object>> mapList = studentService.findeStudentPage(paramMap);
                List<List<Object>> list = new ArrayList<>();
                mapList.forEach(map -> {
                    List<Object> objectList = new ArrayList<>();
                    for(String key : map.keySet()) {
                        objectList.add(map.get(key).toString());
                    }
                    list.add(objectList);
                });
                //分批次写入
                excelWriter.write(list, writeSheet);
            }
            excelWriter.finish();

            long end = System.nanoTime();

            System.out.println("生成excel共耗时：" + (end - start)/1000/1000/1000 + "秒，约：" + (end - start)/1000/1000/1000/60 + "分钟");

            return ResultVOUtil.success();
        } catch (Exception e) {
            log.error("生成excel失败,function=repeatedWrite15w,失败原因：{}", e.toString());
            e.printStackTrace();
            return ResultVOUtil.error();
        }

    }


    public static void main(String[] args) {
        List<List<Object>> list = new ArrayList<List<Object>>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = new ArrayList<Object>();
            data.add("字符串" + i);
            data.add(new Date());
            data.add(0.56);
            list.add(data);
        }
        System.out.println(list);
    }


}